
from flask import render_template, request, flash, redirect, url_for
from . import admin
from .. import db

@admin.route('/admin', methods=['GET', 'POST'])
def adminIndex():
    return render_template('adminIndex.html')

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

Session = sessionmaker()
import os
basedir = os.path.abspath(os.path.dirname(__file__))

dbpath = 'sqlite:///' + os.path.join(basedir, 'ap.db')
engine = create_engine(dbpath, echo=True)
Session.configure(bind=engine)  # 一旦引擎可用

session = Session()



from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Table, Text
from sqlalchemy import Column, Integer, String
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship

Base = declarative_base()



class Users(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    fullname = Column(String(50))
    password = Column(String(12))
    address = relationship("Address", order_by='Address.id', back_populates="user")
    #, order_by=Address.id
     #动态加载数据,用于数据量大的情况
    posts = relationship("BlogPost", back_populates="author", lazy="dynamic")

    def __repr__(self):
        return "<User(name='%s', fullname='%s', password='%s')>" % (
            self.name, self.fullname, self.password)

class Address(Base):
    __tablename__ = 'addresses'
    id = Column(Integer, primary_key=True)
    email_address = Column(String(255), nullable=False)
    user_id = Column(Integer, ForeignKey('user.id'))

    user = relationship("Users", back_populates="address")

    def __repr__(self):
        return "<Address(email_address='%s')>" % self.email_address



# association table
post_keywords = Table('post_keywords', Base.metadata,
	Column('post_id', ForeignKey('posts.id'), primary_key=True),
    Column('keyword_id', ForeignKey('keywords.id'), primary_key=True)
)

class BlogPost(Base):
    __tablename__ = 'posts'
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('user.id'))
    headline = Column(String(255), nullable=False)
    body = Column(Text)
    # many to many BlogPost<->Keyword
    keywords = relationship("Keyword",secondary=post_keywords,
	back_populates="posts")

    author = relationship(Users, back_populates="posts")

    def __init__(self, headline, body, author):
        self.author = author
        self.headline = headline
        self.body = body

    def __repr__(self):
        return "BlogPost(%r, %r, %r)" % (self.headline, self.body, self.author)


class Keyword(Base):
    __tablename__ = 'keywords'
    id = Column(Integer, primary_key=True)
    keyword = Column(String(50), nullable=False, unique=True)
    posts = relationship(BlogPost,
	secondary=post_keywords,
	back_populates='keywords')

    def __init__(self, keyword):
        self.keyword = keyword

@admin.route('/test3', methods=['GET', 'POST'])
def test3():
    Base.metadata.create_all(engine)

    jack = Users(name='wendy', fullname='wendy Bean', password='gjffdd')

    jack.address = [Address(email_address='wendy@google.com'),Address(email_address='j25@yahoo.com')]
    #session.add(jack)
    #session.commit()

    wendy = session.query(Users).\
    filter_by(name='wendy').one()
    post = BlogPost("Wendy's Blog Post", "This is a test", wendy)
    #session.add(post)
    #post.keywords.append(Keyword('wendz'))
    #post.keywords.append(Keyword('first2post'))
    session.commit()

    re = session.query(BlogPost).\
    filter(BlogPost.keywords.any(keyword='firstpost')).\
    all()
    print(re)


    re = session.query(BlogPost).\
    filter(BlogPost.author==wendy).\
    filter(BlogPost.keywords.any(keyword='firstpost')).\
    all()
    print(re)

    re = wendy.posts.\
    filter(BlogPost.keywords.any(keyword='firstpost')).\
    all()
    print(re)



    return "666"


@admin.route('/test2', methods=['GET', 'POST'])
def test2():
    Base.metadata.create_all(engine)

    jack = Users(name='jack', fullname='Jack Bean', password='gjffdd')

    jack.address = [Address(email_address='jack@google.com'),Address(email_address='j25@yahoo.com')]

    print(jack.address[1])
    print(jack.address[1].user)

    # session.add(jack)
    # session.commit()
    # print(jack)
    # print(jack.address)

    jack = session.query(Users).\
	    filter_by(name='jack').one()
    print(jack)
    print(jack.address)


    for u, a in session.query(Users, Address).\
            filter(Users.id==Address.user_id).\
            filter(Address.email_address=='jack@google.com').\
            all():
        print(u)
        print(a)


    re = session.query(Users).join(Address).\
        filter(Address.email_address=='jack@google.com').\
        all()
    print(re)

    from sqlalchemy.orm import aliased
    adalias1 = aliased(Address)
    adalias2 = aliased(Address)
    for username, email1, email2 in \
            session.query(Users.name, adalias1.email_address, adalias2.email_address).\
            join(adalias1, Users.address).\
            join(adalias2, Users.address).\
            filter(adalias1.email_address=='jack@google.com').\
            filter(adalias2.email_address=='j25@yahoo.com'):
        print(username, email1, email2)


    from sqlalchemy.sql import func
    stmt = session.query(Address.user_id, func.count('*').\
        label('address_count')).\
        group_by(Address.user_id).subquery()

    for u, count in session.query(Users, stmt.c.address_count).\
        outerjoin(stmt, Users.id==stmt.c.user_id).order_by(Users.id):
        print(u, count)


    stmt = session.query(Address).\
    filter(Address.email_address != 'j25@yahoo.com').\
    subquery()

    adalias = aliased(Address, stmt)
    for user, address in session.query(Users, adalias).\
    join(adalias, Users.address):
        print(user)
        print(address)

    from sqlalchemy.sql import exists
    stmt = exists().where(Address.user_id==Users.id)
    for name, in session.query(Users.name).filter(stmt):
        print(name)


    for name, in session.query(Users.name).\
        filter(Users.address.any()):
        print(name)

    session.query(Address).\
    filter(~Address.user.has(Users.name=='jack')).all()

    from sqlalchemy.orm import subqueryload
    jack = session.query(Users).\
    options(subqueryload(Users.address)).\
    filter_by(name='jack').one()

    from sqlalchemy.orm import joinedload
    jack = session.query(Users).\
    options(joinedload(Users.address)).\
    filter_by(name='jack').one()

    from sqlalchemy.orm import contains_eager
    jacks_address = session.query(Address).\
    join(Address.user).\
    filter(Users.name=='jack').\
    options(contains_eager(Address.user)).all()


    return "5555"

@admin.route('/test', methods=['GET', 'POST'])
def test():
    Base.metadata.create_all(engine)
    ed_user = Users(name='ed', fullname='Ed Jones', password='edspassword')

    re = session.add(ed_user)
    session.add_all([
	    Users(name='wendy', fullname='Wendy Williams', password='foobar'),
	    Users(name='mary', fullname='Mary Contrary', password='xxg527'),
	    Users(name='fred', fullname='Fred Flinstone', password='blah')
	])
    session.commit()
    ed_user.password = 'f8s7ccs'
    session.dirty
    print(session.new)

    ed_user.name = 'Edwardo'
    fake_user = Users(name='fakeuser', fullname='Invalid', password='12345')
    session.add(fake_user)
    print(ed_user)
    session.commit()

    #print(re)
    #print(ed_user)
    our_user = session.query(Users).filter_by(name='ed').all()
    all_user = session.query(Users).all()# doctest:+NORMALIZE_WHITESPACE
    somes = session.query(Users).filter(Users.name.in_(['Edwardo', 'fakeuser'])).all()
    #print(somes)
    #print(Users.__table__)
    print(ed_user.id)
    print(dbpath)
    #session.rollback()
    #print(all_user)
    #for row in session.query(Users).all():
	    #print(row)

    #user = session.query(Users).filter(Users.id == 99).one_or_none()
    #print(user)

    query = session.query(Users.id).filter(Users.name == 'mary').order_by(Users.id)
    #u = query.scalar()
    #print(u)

    from sqlalchemy import text
    for user in session.query(Users).\
	        filter(text("id<224")).\
		    order_by(text("id")).all():
	    print(user.name)




    u = session.query(Users).from_statement(
	    text("SELECT * FROM user where name=:name")).\
	    params(name='wendy').all()

    u = session.query(Users).filter(Users.name.like('%dy')).count()

    from sqlalchemy import func
    u = session.query(func.count(Users.name), Users.name).group_by(Users.name).all()

    u = session.query(func.count('*')).select_from(Users).scalar()
    u = session.query(func.count(Users.id)).scalar()

    u = session.query(Users).filter(text("name=:name")).\
	    params(name='fakeuser').order_by(Users.id).first()
    print(u)

    return '333'
    #return render_template('adminIndex.html')

